Module ModExcel

    Public Function ImportExcel(ByVal FilePath As String) As DataTable
        Try
            Dim sSheetName As String = "Sheet1"

            Dim con As OleDb.OleDbConnection
            Dim cmd As OleDb.OleDbCommand
            ' Create an OLEDB connection to the Excel Workbook 
            con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & _
                                    ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

            ' Open the connection 
            con.Open()


            Dim ExcelSheets As DataTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "Table"})
            ' Create a Command object to get all data from the Worksheet
            cmd = New OleDb.OleDbCommand("SELECT * FROM [" & ExcelSheets.Rows(0).Item("TABLE_NAME") & "]", con)
            ' Execute the command returning a Data Reader 
            Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
            ' Enuermate all the rows of data in the sheet 
            Dim dt As DataTable = ExcelHeadRowTable()

            While reader.Read()
                Dim dr As DataRow = dt.NewRow
                For Each dc As DataColumn In dt.Columns

                    If IsDBNull(reader.Item(dc.ColumnName)) Then
                        dr(dc.ColumnName) = ""
                    Else                        
                        dr(dc.ColumnName) = CleanStr(reader.Item(dc.ColumnName))
                    End If

                Next
                dt.Rows.Add(dr)
                ' Display the contents of the first column 
                'Debug.Print(reader.GetString(1))

            End While
            ' Close the reader 
            reader.Close()
            ' Close the connection 
            con.Close()
            Return dt
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Private Function ExcelHeadRowTable() As DataTable
        Dim ObjectsTable As DataTable = New DataTable("DataTable")
        Dim currentRow() As String = {"_RECORD_ID", "LETTER_CODE", _
                                        "CURRENT_RUN_NUMBER", "SYSTEM", "AMOUNT_DUE", "POLICY_NUMBER", "PRODUCT_TYPE", _
                                        "MAILING_NAME", "MAILING_ADDRESS_1", "MAILING_ADDRESS_2", "MAILING_ADDRESS_3", _
                                        "MAILING_SUBURB", "MAILING_STATE", "MAILING_COUNTRY", "MAILING_POSTCODE", "ADVISOR_FORENAME", _
                                        "ADVISER_SURNAME", "ADVISER_AGENT_NUMBER", "ADVISER_CLIENT_NUMBER", "ADVISER_EMAIL_ADDRESS", _
                                        "ADVISER_PHONE_NUMBER", "LETTER_DATE", "DUE_DATE", "EXCEPTION_IDENTIFIER", _
                                        "NEW_PREMIUM_AMOUNT", "SUPERANNUATION_INDICATOR", "PAYMENT_FREQUENCY", "CURRENT_PREMIUM_AMOUNT"}
        Try
            Dim currentField As String

            For Each currentField In currentRow
                If Trim(currentField) <> "" Then
                    Dim fObject As DataColumn = New DataColumn
                    fObject.DataType = System.Type.GetType("System.String")
                    fObject.ColumnName = Trim(currentField)
                    fObject.DefaultValue = ""
                    ObjectsTable.Columns.Add(fObject)
                End If
            Next
            Return ObjectsTable
        Catch ex As Exception
            Throw ex
        End Try

    End Function
End Module
